package batch.makeFile;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.lang.ObjectUtils;

import org.apache.log4j.*;
import java.sql.Connection;
import java.util.List;

import java.sql.*;
import java.io.*;
import java.util.*;
import java.util.HashMap;
import java.lang.Integer;
import java.util.Date;

public class makeListWithEmail
{
    final String TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");

    private String fileName = "";
    private String dataPath = "/home01/sfc/batch/makeFile/data/file/";
    //private final String ORAURL = "jdbc:oracle:thin:@eu-stgdb:1521:ORAEU";
    private final String ORAURL = "jdbc:oracle:thin:@eu-anydb:1521:ORAEU";

    public makeListWithEmail() {
        ;
    }

    public static void main(String [] args) {
        makeListWithEmail obj = null;

        if(args == null || args.length < 1 || args.length > 1) {
            System.out.println("java makeListWithEmail Exception == Missed FileName Parameter");
            System.exit(-1);
        }
        else{
            obj = new makeListWithEmail();
            obj.fileName = args[0];
        }

        Logger.getLogger("makefile.makeListWithEmail").info("( * ) Start.");
        Logger.getLogger("makefile.makeListWithEmail").info("fileName == " + obj.fileName);
        obj.startPG();
        Logger.getLogger("makefile.makeListWithEmail").info("( * ) End.");
    }

    public void startPG(){
        HashMap hData =  null;

        Connection conn = null;
        QueryRunner qRunner = null;

        OutputStreamWriter oswFile = null;

        String fileNm  = "/home01/sfc/batch/makeFile/data/gbr_member_fromEmail_"  + TODAY + ".txt";

        String strEmail;

        StringBuffer sbTemp = new StringBuffer();

        int iNonUser = 0;
        int iUpUser = 0;

//brand, operator
        StringBuffer sQuery = new StringBuffer()
                             .append("SELECT ID                        \n")
                             .append("     , FIRSTNAME                 \n")
                             .append("     , LASTNAME                  \n")
                             .append("     , EMAIL                     \n")
                             .append("     , GENDER                    \n")
                             .append("     , BIRTH                     \n")
                             .append("     , B.NAME HP_BRAND           \n")
                             .append("     , HP_MODEL                  \n")
                             .append("     , HP_NUM                    \n")
                             .append("     , C.NAME HP_PROVIDER        \n")
                             .append("     , HP_BUY_YM                 \n")
                             .append("     , REG_COUNTRY               \n")
                             .append("  FROM G_MB_MEMBER   A           \n")
                             .append("     , G_GBR_CO_CODE B           \n")
                             .append("     , G_GBR_CO_CODE C           \n")
                             .append(" WHERE A.HP_BRAND    = B.CODE    \n")
                             .append("   AND A.HP_PROVIDER = C.CODE(+) \n")
                             .append("   AND A.U_EMAIL     = ?         \n");


        try {
            hData = this.getInfo();

            if(hData == null && hData.size() < 1) {
                throw new Exception("No Data");
            }

            int iSize = hData.size();

            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(ORAURL, "gsms", "any02gs");
            conn.setAutoCommit(false);

            qRunner = new QueryRunner();

            ResultSetHandler rsh = new ArrayListHandler();

            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm, false), "ISO-8859-1");

            sbTemp.append("ID"              ).append("|")
                  .append("FIRST NAME"      ).append("|")
                  .append("LAST NAME"       ).append("|")
                  .append("EMAIL"           ).append("|")
                  .append("GENDER"          ).append("|")
                  .append("D.O.B"           ).append("|")
                  .append("HP_BRAND"        ).append("|")
                  .append("HP_MODEL"        ).append("|")
                  .append("HP_NUM"          ).append("|")
                  .append("OPERATOR"        ).append("|")
                  .append("DATE OF PURCHASE").append("|")
                  .append("REG_COUNTRY"     ).append("\n");

            oswFile.write(sbTemp.toString());

            sbTemp.setLength(0);

            for(int i=0;i<iSize;i++) {
                strEmail = (String)hData.get(new Integer(i));

                List lsTemp = (List)qRunner.query(conn, sQuery.toString(), strEmail.toLowerCase(), rsh);

                if(lsTemp.size() > 0){
                    for(Iterator j = lsTemp.iterator(); j.hasNext();){
                        Object[] sTemp = (Object[])j.next();

                        sbTemp.setLength(0);

                        for(int x=0;x<sTemp.length;x++){

                            sbTemp.append(ObjectUtils.toString(sTemp[x], ""));

                            if( x == sTemp.length - 1 ){
                                sbTemp.append("\n");
                            }
                            else{
                                sbTemp.append("|");
                            }
                        }

                        oswFile.write(sbTemp.toString());

                        iUpUser++;
                    }
                }
                else{
                    Logger.getLogger("makefile.makeListWithEmail").info("No User : " + strEmail);
                    iNonUser++;
                }
            }

            conn.commit();

            Logger.getLogger("makefile.makeListWithEmail").info("Total Count : " + iSize);
            Logger.getLogger("makefile.makeListWithEmail").info("Update Count : " + iUpUser);
            Logger.getLogger("makefile.makeListWithEmail").info("No User Count : " + iNonUser);
        }
        catch(Exception ex) {
            if(conn != null) try { conn.rollback(); } catch (Exception ee) {}
            ex.printStackTrace();

            Logger.getLogger("makefile.makeListWithEmail").info(ex.toString());
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("makefile.makeListWithEmail").info(stElement[p].toString());
            }
        }
        finally {
            if(conn != null) try { conn.close(); } catch (Exception ee) { ee.printStackTrace();  }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }

    public HashMap getInfo() throws Exception {
        HashMap hMap = new HashMap();
        BufferedReader in = null;
        String line = "";
        int i = 0;

        try {
            in = new BufferedReader(new FileReader(dataPath + fileName));

            while((line = in.readLine()) != null){
                hMap.put(new Integer(i), StringUtils.removeEnd(StringUtils.removeStart(line, "'"), "'").toLowerCase());
                i++;
            }
        }
        finally {
            if(in != null) try { in.close(); } catch(Exception e) {}
        }

        return hMap;
    }
}
